package com.cmcc.zysoft.infocollection.dao;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import org.springframework.util.StringUtils;

import com.cmcc.zysoft.infocollection.model.News;
import com.starit.common.dao.hibernate.HibernateBaseDaoImpl;
import com.starit.common.dao.support.Pagination;


/**
 * NewsDao.java
 * @author zhou.dong
 * @2012-12-18
 */

@Repository
public class NoticeDao extends HibernateBaseDaoImpl<News, String> {	
	
	@Resource
	private JdbcTemplate jdbcTemplate;
	/**
	 * 获取相应条件的news列表
	 * @param page
	 * @param rows
	 * @param newsId
	 * @return
	 */
	public Map <String,Object> getNewList(int page, int rows) {
		int offset = (page-1)*rows;
		Map<String,Object> map = new HashMap<String,Object>();
		String rowSql ="SELECT  b.*, tbn.read_num,emp.employee_name FROM tb_b_news b,tb_b_read_news tbn ,tb_c_employee emp,tb_c_system_user sys  WHERE b.news_id=tbn.news_id AND b.news_type='0' AND tbn.news_type='0' AND b.state='1' AND emp.employee_id=sys.employee_id AND sys.user_id=b.release_man ORDER BY b.release_date  DESC LIMIT ?,?";
		String countSql ="SELECT COUNT(*) FROM tb_b_news b,tb_b_read_news tbn ,tb_c_employee emp,tb_c_system_user sys  WHERE b.news_id=tbn.news_id AND b.news_type='0' AND tbn.news_type='0' AND b.state='1' AND emp.employee_id=sys.employee_id AND sys.user_id=b.release_man";
		
		List<Map<String,Object>> result = this.jdbcTemplate.queryForList(rowSql,offset,rows);
		int total = this.jdbcTemplate.queryForInt(countSql);
		map.put("rows", result);
		
		map.put("total", total);
		return map;
	}
	
	/**
	 * 查出所有未阅读的newslist
	 * @param page
	 * @param rows
	 * @return
	 */
	public Map<String,Object> getNOReadNewList(int page,int rows){
		int offset =(page-1)*rows;
		Map<String,Object>map = new HashMap<String,Object>();
		String rowSql = "SELECT  b.*, tbn.read_num,emp.employee_name FROM tb_b_news b,tb_b_read_news tbn ,tb_c_employee emp,tb_c_system_user sys  WHERE b.news_id=tbn.news_id AND b.news_type='0' AND tbn.news_type='0' AND b.state='1' AND emp.employee_id=sys.employee_id AND sys.user_id=b.release_man AND tbn.read_num='0'ORDER BY b.release_date  DESC LIMIT ?,?";
		String countSql="select COUNT(*) FROM tb_b_news b,tb_b_read_news tbn ,tb_c_employee emp,tb_c_system_user sys  WHERE b.news_id=tbn.news_id AND b.news_type='0' AND tbn.news_type='0' AND b.state='1' AND emp.employee_id=sys.employee_id AND sys.user_id=b.release_man AND tbn.read_num='0'";
		List<Map<String,Object>> result = this.jdbcTemplate.queryForList(rowSql,offset,rows);
		int total = this.jdbcTemplate.queryForInt(countSql);
		map.put("rows", result);
		map.put("total", total);
		return map;
		
	}
	
	/**
	 * 返回未阅读公告数目
	 * @return
	 */
	public int getNOReadNewList(){
		String rowSql ="select COUNT(*) FROM tb_b_news b,tb_b_read_news tbn ,tb_c_employee emp,tb_c_system_user sys  WHERE b.news_id=tbn.news_id AND b.news_type='0' AND tbn.news_type='0' AND b.state='1' AND emp.employee_id=sys.employee_id AND sys.user_id=b.release_man AND tbn.read_num='0'";
		return this.jdbcTemplate.queryForInt(rowSql);
	}
	
	/**
	 * 查出所有已发送的公告
	 * @param page
	 * @param rows
	 * @param userId
	 * @return
	 */
	public Map<String,Object> getSendNewList(int page,int rows,String userId){
		int offset =(page-1)*rows;
		Map<String,Object>map = new HashMap<String,Object>();
		String rowSql ="SELECT  b.*, tbn.read_num,emp.employee_name FROM tb_b_news b,tb_b_read_news tbn ,tb_c_employee emp,tb_c_system_user sys  WHERE b.news_id=tbn.news_id AND b.news_type='0' AND tbn.news_type='0' AND b.state='1' AND emp.employee_id=sys.employee_id AND sys.user_id='"+userId+"'ORDER BY b.release_date  DESC LIMIT ?,?";
		String countSql="SELECT COUNT(*) FROM tb_b_news b,tb_b_read_news tbn ,tb_c_employee emp,tb_c_system_user sys  WHERE b.news_id=tbn.news_id AND b.news_type='0' AND tbn.news_type='0' AND b.state='1' AND emp.employee_id=sys.employee_id AND sys.user_id='"+userId+"' ";
		List<Map<String,Object>> result = this.jdbcTemplate.queryForList(rowSql,offset,rows);
		int total = this.jdbcTemplate.queryForInt(countSql);
		map.put("rows", result);
		map.put("total", total);
		return map;
		
	}
	
	/**
	 * 返回已发送公告的数目
	 * @param userId
	 * @return
	 */
	public int sendNewsCount(String userId){
		String countSql="SELECT COUNT(*) FROM tb_b_news b,tb_b_read_news tbn ,tb_c_employee emp,tb_c_system_user sys  WHERE b.news_id=tbn.news_id AND b.news_type='0' AND tbn.news_type='0' AND b.state='1' AND emp.employee_id=sys.employee_id AND sys.user_id='"+userId+"' ";
		int total = this.jdbcTemplate.queryForInt(countSql);
		return total;
	}
	
	
	/**
	 * 返回草稿的列表
	 * @param page
	 * @param rows
	 * @param userId
	 * @return
	 */
	public Map<String,Object> getDraftList (int page,int rows,String userId){
		int offset =(page-1)*rows;
		Map<String,Object>map = new HashMap<String,Object>();
		String rowSql ="SELECT tbn.*,emp.employee_name FROM tb_b_news tbn, tb_c_system_user sys ,tb_c_employee emp WHERE tbn.state='0' AND tbn.news_type='0'AND sys.employee_id=emp.employee_id  AND sys.user_id='"+userId+"' ORDER BY tbn.release_date DESC limit ?,?";
		String countSql="SELECT COUNT(*) FROM tb_b_news tbn, tb_c_system_user sys ,tb_c_employee emp WHERE tbn.state='0' AND tbn.news_type='0'AND sys.employee_id=emp.employee_id  AND sys.user_id='"+userId+"'";
		List<Map<String,Object>> result = this.jdbcTemplate.queryForList(rowSql,offset,rows);
		int total = this.jdbcTemplate.queryForInt(countSql);
		map.put("rows", result);
		map.put("total", total);
		return map;
		
	}
	
	/**
	 * 返回草稿的数目
	 * @param userId
	 * @return
	 */
	public int newsDraftCount(String userId){
		String countSql="SELECT COUNT(*) FROM tb_b_news tbn, tb_c_system_user sys ,tb_c_employee emp WHERE tbn.state='0' AND tbn.news_type='0'AND sys.employee_id=emp.employee_id  AND sys.user_id='"+userId+"'";
		int total = this.jdbcTemplate.queryForInt(countSql);
		return total;
	}
	
	public void updateSaveNews(News news){
		/*String sql="update tb_b_news set content="+news.getContent()+", dept_id="+news.getDeptId()+", news_kind_id="+news.getNewsKind()+", release_date="+news.getReleaseDate()+", release_man="+news.getReleaseMan()+", state="+news.getState()+", title="+news.getTitle()+" where news_id="+news.getNewsId();
		jdbcTemplate.execute(sql);	*/
		
	}
	
/**
 * 按照条件查找newsList
 * @param page
 * @param rows
 * @param condition
 * @return
 */
	public Map<String,Object> getNewsListBycondition (int page,int rows,String condition){
		int offset =(page-1)*rows;
		Map<String,Object>map = new HashMap<String,Object>();
		String rowSql ="SELECT  b.*, tbn.read_num,emp.employee_name FROM tb_b_news b,tb_b_read_news tbn ,tb_c_employee emp,tb_c_system_user sys  WHERE b.news_id=tbn.news_id AND b.news_type='0' AND tbn.news_type='0' AND b.state='1' AND emp.employee_id=sys.employee_id AND sys.user_id=b.release_man AND (b.title LIKE '%"+condition.trim()+"%' OR emp.employee_name LIKE '%"+condition.trim()+"%')  ORDER BY b.release_date DESC LIMIT ?,? ";
		String countSql="SELECT  COUNT(*) FROM tb_b_news b,tb_b_read_news tbn ,tb_c_employee emp,tb_c_system_user sys  WHERE b.news_id=tbn.news_id AND b.news_type='0' AND tbn.news_type='0' AND b.state='1' AND emp.employee_id=sys.employee_id AND sys.user_id=b.release_man AND (b.title LIKE '%"+condition.trim()+"%' OR emp.employee_name LIKE '%"+condition.trim()+"%')";
		List<Map<String,Object>> result = this.jdbcTemplate.queryForList(rowSql,offset,rows);
		int total = this.jdbcTemplate.queryForInt(countSql);
		map.put("rows", result);
		map.put("total", total);
		return map;
		
	}
	
	
	
	/**
	 * 删除角色信息
	 * 
	 * @param roleId
	 */
	public void deleteNews(String newsId) {
		String sql = " delete from tb_b_news  where newsId = '" + newsId + "'";
		jdbcTemplate.execute(sql);
	}
	
	/**
	 * 判断该用户是否有删除的权限
	 * @param userId
	 * @param newsId
	 * @return 返回0 表示没有，其它未有
	 */
   public int isUserNewsId(String userId ,String newsId){
	   String sql ="select COUNT(*) from tb_b_news tbn where tbn.news_type='0' and tbn.release_man='"+userId+"'and tbn.news_id='"+newsId+"'";
	   int count=this.jdbcTemplate.queryForInt(sql);
	   return count;
   }
	

   /**
	 * 弹出框人员列表
	 * @author yandou
	 * @param page
	 * @param rows
	 * @param idIcon
	 * @param keyword
	 * @return
	 */
	public Pagination<Object> emps(int page, int rows, String idIcon,String keyword) {
		int offset = (page-1)*rows;
		String conditon = "select emp  from SystemUser sy, Employee emp where sy.employeeId=emp.employeeId";
		String countSql= " from SystemUser sy, Employee emp where sy.employeeId=emp.employeeId";
		if(StringUtils.hasText(idIcon)){
			if(idIcon.endsWith("department")){
				conditon += " and emp.departmentId = '"+idIcon.split("_")[0]+"'";
				countSql += " and emp.departmentId = '"+idIcon.split("_")[0]+"'";
			}else{
				conditon += " and emp.departmentId in (select dept.departmentId from Department dept where dept.company.companyId = '"+idIcon.split("_")[0]+"')";
				countSql += " and emp.departmentId in (select dept.departmentId from Department dept where dept.company.companyId = '"+idIcon.split("_")[0]+"')";
			}
		}
		if(StringUtils.hasText(keyword)){
			conditon += " and (emp.employeeName like '%"+keyword+"%' or emp.mobile like '%"+keyword+"%')";
		}
		countSql = "select count(emp.employeeId) " + countSql;
		return this.findPageByHQL(conditon, countSql, offset, rows);
	}
	
	/**
	 * 根据useId返回一个userName
	 * @param userId
	 * @return
	 */
	public String getUserName(String userId){
		String sql="SELECT emp.employee_name FROM tb_c_system_user sys ,tb_c_employee emp WHERE sys.employee_id=emp.employee_id AND sys.user_id='"+userId+"' ";
		return this.jdbcTemplate.queryForObject(sql, String.class);
	}
	
	
	/**
	 * 获取各通知公告条数
	 * @param userId 用户ID
	 * @author jin.congshan
	 * @date 2013-01-08
	 */
    public Map<String, Object> getUserNoticeCount(String userId){
    	String sql = 
    			" SELECT * FROM" +
    			" (SELECT COUNT(*) AS count_not_read FROM tb_b_read_news a WHERE a.user_id = ? AND a.read_num = 0) aa CROSS JOIN" +
    			" (SELECT COUNT(*) AS count_sended FROM tb_b_news a WHERE a.release_man = ? AND a.state = '1') ab CROSS JOIN" +
    			" (SELECT COUNT(*) AS count_draft FROM tb_b_news a WHERE a.release_man = ? AND a.state = '2') ac";
    	return this.jdbcTemplate.queryForMap(sql, userId, userId, userId);
    }
    
    /**
     * 获取通知公告分页列表数据
     * @param userId 用户ID
     * @param search 查询
     * @param page
     * @param rows
     * @author jin.congshan
     * @date 2013-1-9 下午7:41:02
     */
    public Pagination<?> getNoticePageData(String userId, String code, String search, int page, int rows){
    	int offset = (page-1)*rows;
    	String rows_hql = "";
    	String count_hql = "";
    	String cond = "";
    	
    	if("3".equals(code)){
    		rows_hql = 
    			" select new Map(a.newsId as newsId, a.title as title, a.releaseDate as releaseDate, a.state as state, d.employeeName as employeeName)" +
    			" from News a, SystemUser c, Employee d where a.newsType = '0' and a.releaseMan = c.userId and c.employeeId = d.employeeId and a.releaseMan = '"+userId+"'";
    		count_hql = "select count(a.newsId) from News a where a.newsType = '0' and a.releaseMan = '"+userId+"'";
    	} else {
    		rows_hql =
				" select distinct new Map(a.newsId as newsId, a.title as title, a.releaseDate as releaseDate, a.state as state, b.readNewsId as readNewsId, b.readNum as readNum, d.employeeName as employeeName) " +
				" from News a, ReadNews b, SystemUser c, Employee d where a.newsType = '0' and a.newsId = b.newsId and a.releaseMan = c.userId and c.employeeId = d.employeeId";
    		count_hql = "select count(a.newsId) from News a, ReadNews b where a.newsType = '0' and a.newsId = b.newsId";
    	}
    	if(StringUtils.hasText(search)){
    		cond += " and a.title like '%" + search + "%'";
    	}
    	if("0".equals(code)){
    		cond += " and b.userId = '"+userId+"'";
    	} else if("1".equals(code)){
    		cond += " and b.readNum = 0 and b.userId = '"+userId+"'";
    	} else if("2".equals(code)){
    		cond += " and b.userId = '"+userId+"' and a.releaseMan = '"+userId+"'";
    	}
    	rows_hql += cond;
    	count_hql += cond;
    	System.out.println("=========================================");
    	System.out.println(rows_hql);
    	System.out.println(count_hql);
    	return this.findPageByHQL(rows_hql, count_hql, offset, rows);
    }
}
